<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use App\Models\testtab;
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;

//测试分表大数据

class TesttabController extends Controller
{
    protected $field = ['id', 'des', 'des1', 'des3'];

    protected $tabnm;

    protected $years=[];

    public function __construct()
    {
        parent::__construct(); // 必须调用父类构造函数
        $this->tabnm = 'testtab_' . date('Y'); // 在构造函数中初始化
        $currentYear = date('Y');
        //统计最近6年
        $this->years = range($currentYear, $currentYear - 5, -1);

    }

    //生成数据
    public function generate(Request $request)
    {
        $param = json_decode($request->param, true);
        $usr = $this->decodetoken();
        $operator = $usr->name;
        $qty = intval($this->I($param['qty'] ?? 1000000));//默认
        $list = [];
        $count = 1;
        $days = 0;
        $tab = $this->tabnm;
        for ($i = 0; $i < $qty; $i++) {
            if ($days > 1825) {
                $count = 1;
                DB::table($tab)->insert($list);
                $list = [];
                $days = 1;
                $tab = 'testtab_' . date('Y', $timestamp);
            }
            $timestamp = strtotime($days . ' days ago');
            if ($tab != 'testtab_' . date('Y', $timestamp)) {
                $count = 1;
                DB::table($tab)->insert($list);
                $list = [];
            }
            $tab = 'testtab_' . date('Y', $timestamp);
            if ($count > 100) {
                $count = 1;
                DB::table($tab)->insert($list);
                $list = [];
            }
            $list[] = [
                'des' => 'desa' . date('ymd') . '_' . $i,
                'des1' => 'desb' . $i,
                'des3' => 'des3' . $i,
                'tabnm' => $operator,
                'modifier' => $operator,
                'created_at' => date('Y-m-d H:i:s', $timestamp),
            ];
            $count++;
            $days++;
        }
        if (!empty($list)) {
            DB::table($tab)->insert($list);
        }
        return $this->ajaxReturn(['code' => 1, 'msg' => '生成成功!']);
    }
    /**
     * 查询（普通单表查询）
     */
    public function find0(Request $request)
    {
        $param = json_decode($request->param, true);
        $des = $this->I($param['des'] ?? null);
        $des1 = $this->I($param['des1'] ?? null);
        $des3 = $this->I($param['des3'] ?? null);
        $page = intval($this->I($param['page'] ?? 1));
        $pagecount = intval($this->I($param['pagecount'] ?? 50));
        $where = ' a.isdel=0 ';
        if (!empty($des)) {
            $where .= " and a.des = '$des' ";
        }
        if (!empty($des1)) {
            $where .= " and a.des1 = '$des1' ";
        }
        if (!empty($des3)) {
            $where .= " and a.des3 = '$des3' ";
        }
        $tab = $this->tabnm;
        //表不存在自动创建
        $this->ensureTableExists($tab);

        $temp = DB::table($tab . ' as a')
            ->select(['a.id', 'a.des', 'a.des1', 'a.des3', 'a.tabnm', 'modifier', 'updated_at', 'created_at'])
            ->whereRaw($where)
            ->paginate($pagecount, ['id'], 'page', $page)
            ->toArray(); // 控制页码，默认每页 50 条

        $res = $temp['data'];
        $allcount = $temp['total'];
        return $this->ajaxReturn(['code' => 1, 'msg' => '查询成功!', 'count' => $allcount, 'data' => $res]);
    }

    /**
     * 保存
     */
    public function save(Request $request)
    {
        $usr = $this->decodetoken();
        $param = json_decode($request->param, true);
        $id = $this->I($param['id'] ?? null);
        $isnew = false;
        if (empty($id)) {
            $isnew = true;
        }
        $list = [];
        if (empty($param)) {
            return $this->ajaxReturn(['code' => 0, 'msg' => '保存参数为空']);
        }
        foreach ($param as $key => $value) {
            if (in_array($key, $this->field)) {
                $list[$key] = $value;
            }
        }
        if (empty($list)) {
            return $this->ajaxReturn(['code' => 0, 'msg' => '没有需要处理的数据']);
        }
        //新增
        if ($isnew) {
            $list['tabnm'] = $usr->name;
            //新增
            $res = testtab::create($list);
            if (empty($res)) {
                return $this->ajaxReturn(['code' => 0, 'msg' => '新增失败!']);
            }
        } else {
            $list['modifier'] = $usr->name;
            //修改   
            $uptab = testtab::find($id);
            $uptab->update($list);
            $res = $uptab;
        }
        return $this->ajaxReturn(['code' => 1, 'msg' => '保存成功!']);
    }

    /**
     * 删除
     */
    public function delete(Request $request)
    {
        try {
            $param = json_decode($request->param, true);
            $id = $this->I($param['id'] ?? null);
            if (empty($id)) {
                return $this->ajaxReturn(['code' => 0, 'msg' => '删除失败!ID为空']);
            }
            $FSQL = "SELECT a.id,a.name,a.usrgroupid
              FROM UsrGroup a where id in($id)";
            $data = DB::select($FSQL);
            if (empty($data)) {
                return $this->ajaxReturn(['code' => 0, 'msg' => '删除失败!角色不存在']);
            }
            $EXESQL = "UPDATE UsrGroup SET isdel=1 WHERE id in($id)";
            $res = DB::update($EXESQL);
            if (empty($res)) {
                return $this->ajaxReturn(['code' => 0, 'msg' => '删除失败!']);
            }
            $FSQL = "SELECT id,usrgroupid,name,des,tabnm,created_at,modifier,updated_at 
            FROM UsrGroup  where isdel=0";
            $data = DB::select($FSQL);
            //记录日志
            $this->log(['type' => '删除', 'billcaption' => '角色管理', 'des' => '操作人[删除]了角色|[' . $data[0]->name . '],I|D为|[' . $data[0]->usrgroupid . ']']);
            return $this->ajaxReturn(['code' => 1, 'msg' => '删除成功!', 'data' => $data]);
        } catch (\Exception $e) {
            return $this->ajaxReturn(['code' => 0, 'msg' => $this->name . '导入失败：' . $e->getMessage()]);
        }
    }
    //判断表是否存在
    protected function ensureTableExists($tableName)
    {
        if (!Schema::hasTable($tableName)) {
            Schema::create($tableName, function (Blueprint $table) {
                $table->id();
                $table->string('des', 200)->nullable();
                $table->string('des1', 200)->nullable();
                $table->string('des3', 200)->nullable();
                $table->string('tabnm', 50)->nullable();
                $table->string('modifier', 50)->nullable();
                $table->integer('isdel')->nullable()->default(0);
                $table->timestamp('updated_at')->default(DB::raw('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'));
                $table->timestamp('created_at')->nullable();
            });
        }
    }


    //框架分页(速度比原生的差)
    public function find1(Request $request)
    {
        $param = json_decode($request->param, true);
        //当前页数量
        $pagecount = intval($this->I($param['pagecount'] ?? 100));
        //最后一页信息
        $lastdata = $this->I($param['lastdata'] ?? null);
        //第一页信息
        $firstdata = $this->I($param['firstdata'] ?? null);
        //（0当前页 1下一页  2 上一页）
        $pagetype = $this->I($param['pagetype'] ?? 0);
        $where = '';
        if ($pagetype == 2) {
            //下一页
            $lastId = $lastdata['id'] ?? null;
            $lastDate = $lastdata['created_at'] ?? null;
            $where = " and (created_at < '$lastDate' or (created_at = '$lastDate' and id < '$lastId'))";
        } else if ($pagetype == 1) {
            //上一页
        }

        // 第一步：获取所有符合条件的ID（用于分页）
        $unionQuery = null;
        foreach ($this->years as $year) {
            $table = 'testtab_' . $year;
            $q = DB::table($table)->select('id', 'created_at');
            if ($pagetype == 2) {
                $lastId = $lastdata['id'] ?? null;
                $lastDate = $lastdata['created_at'] ?? null;
                $q->where(function ($query) use ($lastId, $lastDate) {
                    $query->where('created_at', '<', $lastDate)
                        ->orWhere(function ($q2) use ($lastId, $lastDate) {
                            $q2->where('created_at', $lastDate)
                                ->where('id', '<', $lastId);
                        });
                });
            }
            $unionQuery = $unionQuery ? $unionQuery->unionAll($q) : $q;
        }
        // 获取总记录数
        $total = DB::query()->fromSub($unionQuery, 'union_table')->count();

        // 获取当前页的ID（按created_at和id排序）
        $paginatedIds = DB::query()->fromSub($unionQuery, 'union_table')
            ->orderBy('created_at', 'desc')
            ->orderBy('id', 'desc')
            ->paginate($pagecount);

        // 第二步：根据分页ID获取完整数据
        $items = collect();
        foreach ($this->years as $year) {
            $table = 'testtab_' . $year;
            $items = $items->merge(
                DB::table($table)
                    ->whereIn('id', $paginatedIds->pluck('id'))
                    ->get()
            );
        }
        // 按创建时间和ID排序
        $items = $items->sortByDesc(function ($item) {
            return [$item->created_at, $item->id];
        })->values();
        return $this->ajaxReturn(['code' => 1, 'msg' => '查询成功!', 'pagesize' => ceil($total / $pagecount), 'count' => $total, 'firsdata' => $items[0] ?? [], 'lastdata' => $items[count($items) - 1] ?? [], 'data' => $items]);
    }

    //原生SQL分表分页查询
    public function find(Request $request)
    {
        try {
            $param = json_decode($request->param, true);
            //第几页
            $page = intval($this->I($param['page'] ?? 100));
            //当前页数量
            $pagecount = intval($this->I($param['pagecount'] ?? 100));
            //最后一页信息
            $lastdata = $this->I($param['lastdata'] ?? null);
            //第一页信息
            $firstdata = $this->I($param['firstdata'] ?? null);
            //（0当前页 1下一页  2 上一页）
            $pagetype = $this->I($param['pagetype'] ?? 0);
            $where = '';
            if ($pagetype == 2) {
                //下一页
                $lastId = $lastdata['id'] ?? null;
                $lastDate = $lastdata['created_at'] ?? null;
                $where = " and (created_at < '$lastDate' or (created_at = '$lastDate' and id < '$lastId'))";
            } else if ($pagetype == 1) {
                //上一页
                $firstId = $firstdata['id'] ?? null;
                $firstDate = $firstdata['created_at'] ?? null;
                $where = " and (created_at > '$firstDate' or (created_at = '$firstDate' and id > '$firstId'))";
            }
            $FSQLcount = "";
            foreach ($this->years as $year) {
                $table = 'testtab_' . $year;
                $FSQLcount .= "SELECT COUNT(id) AS total
                FROM $table where 1=1
                UNION ALL ";
            }
            $FSQLcount = substr($FSQLcount, 0, strlen($FSQLcount) - 10);
            $FSQLcount = "SELECT *
            FROM ($FSQLcount)a;";
            $countdata=DB::select($FSQLcount);

            $total=0;
            foreach ($countdata as $key => $value) {
                $total += intval($value->total);
            }
            $pagesize=ceil($total / $pagecount);

            //加载不同分表所占比的页数
            $counthanlde=[];
            $currentPageStart = 1;
            $i=0;
            $remainder=0;
            foreach($countdata as $key){
                if ($key->total == 0) {
                    $i++;
                    continue; // 跳过空表
                }
                $pages = intval(($key->total+$remainder) / $pagecount);
                $remainder = $key->total % $pagecount;
                if ($remainder > 0) {
                    $pages += 1;
                }
                $counthanlde[]=[
                    'year'=>$this->years[$i],
                    'total'=>$key->total,
                    'pagebegin'=>$currentPageStart,
                    'pageend'=>$currentPageStart + $pages - 1,
                ];
                if ($remainder > 0) {
                    $pages--;
                }
                $currentPageStart += $pages;
                $i++;
            }
            //根据要查找的页签判断要不要查其它分表 
            $FSQL='';
            foreach($counthanlde as $key){
                if($key['pagebegin']<=$page && $page<=$key['pageend']){
                    $table = 'testtab_' . $key['year'];
                    $FSQL .= " (SELECT id,des,des1,des3,tabnm,modifier,created_at
                    FROM $table where 1=1  $where  ORDER BY created_at DESC, id DESC LIMIT 100
                    )
                    UNION ALL ";
                }
            }
            $FSQL = substr($FSQL, 0, strlen($FSQL) - 10);
            $FSQL = " SELECT  *
            FROM ($FSQL)a
            ORDER BY created_at DESC, id DESC
            LIMIT $pagecount;";
            $items = DB::select($FSQL);
            return $this->ajaxReturn(['code' => 1, 'msg' => '查询成功!', 'pagesize' => $pagesize, 'count' => $total, 'firsdata' => $items[0] ?? [], 'lastdata' => $items[count($items) - 1] ?? [], 'data' => $items]);
        } catch (\Exception $e) {
            return $this->ajaxReturn(['code' => 0, 'msg' => '失败!', 'error' => $e->getMessage(), 'FSQL' => $FSQL]);
        }
    }

}
